SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Function [dbo].[OutAdjustAmount](@DrugId nvarchar(50),@Style varchar(1),@fromdate datetime,@todate datetime)
Returns @OutAdjustAmount table
(
	Quantity int,
	Amount decimal
)
as
Begin
declare @Outadjust_total int
	if(@style='')
		Insert @OutAdjustAmount select  sum(quantity) as Quantity,Sum(Quantity*UnitPrice) as TotalAmount
		from dbo.StockOutDetails AS SD INNER JOIN dbo.StockOut S ON S.StockOutId=SD.StockOutId
		where StockOutDate between @fromdate and @todate and Drugid=@DrugId and S.IsComplete=1
		group by DrugId
	else
		Insert @OutAdjustAmount select  sum(quantity) as Quantity,Sum(Quantity*UnitPrice) as TotalAmount
		from dbo.StockOutDetails AS SD INNER JOIN dbo.StockOut S ON S.StockOutId=SD.StockOutId
		where StockOutDate between @fromdate and @todate and Drugid=@DrugId and S.IsComplete=1 And left(SD.StockOutId,1)=@style
		group by DrugId
	return 
END

GO
